/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Administrator
 */
public class AbDAO {
    protected Connection m_Conn;
    protected Statement m_Sm;
    protected void DongKetNoi(){
        try {
            m_Sm.close();
            m_Conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
    }
    protected void KetNoi() throws IOException{
        String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
        try {
            try {
                Class.forName(DBDriver).newInstance();
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(null, ex.getMessage());
            }
        } catch (InstantiationException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        } catch (IllegalAccessException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
       //String file = "../Database/CSDL JavaMail.mdb";
        String file = new java.io.File(".").getCanonicalPath();
        //file =
        file+="\\Database\\CSDL JavaMail.mdb";
        //file = this.getClass().getResource("/Database/").getPath().replace("%20", " ") + "CSDL JavaMail.mdb";
        //String file = "G:\\New Folder\\DoAnLyThuyet_JavaOutLook\\src\\Database\\CSDL JavaMail.mdb";
        String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+file;
        try {
            m_Conn = DriverManager.getConnection(url);
        } catch (SQLException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
    }

    protected ResultSet thucThiTraVeResultSet (String CauTruyVan){
        try {
            m_Sm = m_Conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
        ResultSet rs = null;
        try {
             rs = m_Sm.executeQuery(CauTruyVan);
        } catch (SQLException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
        return rs;
    }
    /**
     * thuc thi insert, update, delete
     * @param CauTruyVan    cau sql
     * @return  so dong bi tac dong, neu la cau insert thi tra ve autonumber vua duoc tao
     */
    protected int thucThiUpdate (String CauTruyVan){
        try {
            m_Sm = m_Conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
        int rs = -1;
        try {
             rs = m_Sm.executeUpdate(CauTruyVan);
             if (CauTruyVan.trim().startsWith("insert"))
                 rs = layAutonumberMoiThem(m_Sm);
             
        } catch (SQLException ex) {
            Logger.getLogger(AbDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
        return rs;
    }
    /**
     * Lay autonumbuer vua moi duoc tao ra do cau lenh insert
     * @param sm            Statement goi cau lenh insert
     * @return              autonumber duoc tao
     * @throws java.sql.SQLException
     */
    public static int layAutonumberMoiThem (Statement sm) throws SQLException{
         int Kq = -1;
         ResultSet gKeysrs = sm.executeQuery("SELECT @@IDENTITY As TheNewId");
         while (gKeysrs.next()){
                 Kq = gKeysrs.getInt(1);
         }
         return Kq;
    }
}
